package parkingManage.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import parkingManage.dao.RecordDao;
import parkingManage.model.RecordModel;
import parkingManage.utils.DBUtils;

public class RecordDaoImpl implements RecordDao {

	@Override
	public List<RecordModel> selectAll() {
		List<RecordModel> list = new ArrayList<RecordModel>();

		// 获取连接
		Connection conn = DBUtils.getConnection();
		Statement sta = null;
		ResultSet result = null;

		try {
			// 获取状态集
			sta = conn.createStatement();
			String sql = "select * from record where free > 0";
			// 获取结果集
			result = sta.executeQuery(sql);
			while (result.next()) {
				int id = result.getInt("id");
				String carLicense = result.getString("car_license");
				int lotCode = result.getInt("parking_lot");
				LocalDateTime staTime = result.getTimestamp("start_time").toLocalDateTime();
				LocalDateTime leaTime = result.getTimestamp("leave_time").toLocalDateTime();
				BigDecimal free = new BigDecimal(result.getString("free"));
				RecordModel record = new RecordModel(id, carLicense, lotCode, staTime, leaTime, free);
				list.add(record);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(sta, conn, result);
		}

		return list;
	}

	@Override
	public List<RecordModel> unpaid() {
		// 获取连接
		Connection conn = DBUtils.getConnection();

		List<RecordModel> list = new ArrayList<RecordModel>();
		Statement sta = null;
		ResultSet resultSet = null;
		try {
			// 获取状态集
			sta = conn.createStatement();

			String sql = "select id,car_license,parking_lot,start_time from record where free=0";

			// 获取结果集
			resultSet = sta.executeQuery(sql);

			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String carLicense = resultSet.getString("car_license");
				int lotCode = resultSet.getInt("parking_lot");
				LocalDateTime staTime = resultSet.getTimestamp("start_time").toLocalDateTime();

				RecordModel recordModel = new RecordModel(id, carLicense, lotCode, staTime, null, null);
				list.add(recordModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(sta, conn, resultSet);
		}
		return list;
	}

	@Override
	public int addRecord(String carLicense, int lotCode, LocalDateTime startTime) {
		// 获取连接
		Connection conn = DBUtils.getConnection();

		PreparedStatement ps = null;

		String sql = "insert into record(car_license,parking_lot,start_time) value(?,?,?)";
		try {
			ps = conn.prepareStatement(sql);

			ps.setString(1, carLicense);
			ps.setInt(2, lotCode);
			ps.setObject(3, startTime);

			int k = ps.executeUpdate();
			return k;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(ps, conn, null);
		}
		return 0;
	}

	@Override
	public List<RecordModel> selectByCarFree(String carLicense) {
		// 获取连接
		Connection conn = DBUtils.getConnection();

		List<RecordModel> list = new ArrayList<RecordModel>();
		PreparedStatement ps = null;
		ResultSet result = null;

		String sql = "select * from record where free>0 and car_license=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, carLicense);
			// 获取结果集
			result = ps.executeQuery();
			while (result.next()) {
				int id = result.getInt("id");
				int lotCode = result.getInt("parking_lot");
				LocalDateTime staTime = result.getTimestamp("start_time").toLocalDateTime();
				LocalDateTime leaveTime = result.getTimestamp("leave_time").toLocalDateTime();
				BigDecimal free = result.getBigDecimal("free");

				RecordModel recordModel = new RecordModel(id, carLicense, lotCode, staTime, leaveTime, free);
				list.add(recordModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(ps, conn, result);
		}
		return list;
	}

	@Override
	public List<RecordModel> selectByCarUnfree(String carLicense) {
		// 获取连接
		Connection conn = DBUtils.getConnection();

		List<RecordModel> list = new ArrayList<RecordModel>();
		PreparedStatement ps = null;
		ResultSet result = null;

		String sql = "select id,car_license,parking_lot,start_time from record where free=0 and car_license=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, carLicense);
			// 获取结果集
			result = ps.executeQuery();
			while (result.next()) {
				int id = result.getInt("id");
				int lotCode = result.getInt("parking_lot");
				LocalDateTime staTime = result.getTimestamp("start_time").toLocalDateTime();

				RecordModel recordModel = new RecordModel(id, carLicense, lotCode, staTime, null, null);
				list.add(recordModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(ps, conn, result);
		}
		return list;

	}

	@Override
	public RecordModel pickupInfo(String license, int lotCode) {
		Connection conn = DBUtils.getConnection();

		PreparedStatement ps = null;
		ResultSet result = null;

		String sql = "select id,car_license,parking_lot,start_time from record where car_license = ? and parking_lot =? and free = 0";

		try {
			ps = conn.prepareStatement(sql);

			ps.setString(1, license);
			ps.setInt(2, lotCode);
			result = ps.executeQuery();
			while (result.next()) {
				int id = result.getInt("id");
				String carLicense = result.getString("car_license");
				int lot = result.getInt("parking_lot");
				LocalDateTime staTime = result.getTimestamp("start_time").toLocalDateTime();

				RecordModel record = new RecordModel(id, carLicense, lot, staTime, null, null);

				return record;

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(ps, conn, result);
		}

		return null;
	}

	@Override
	public int pickup(int id, LocalDateTime leaTime, BigDecimal free) {
		Connection conn = DBUtils.getConnection();

		PreparedStatement ps = null;
		String sql = "update record set free=?,leave_time=? where id=?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setBigDecimal(1, free);
			ps.setObject(2, leaTime);
			ps.setInt(3, id);

			int k = ps.executeUpdate();

			return k;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.closeAll(ps, conn, null);
		}
		return 0;
	}

}
